from Log import log, notice, error
import datetime

def level_check(mysql,opusername,need):
    success, result, _ = mysql.query("SELECT tid FROM teacher WHERE tusername = '{}';".format(str(opusername)))
    if success:
        if result:
            log("id存在")
            success, result, level = mysql.query(
                "SELECT level FROM teacher WHERE tusername = '{}';".format(str(opusername)))
            if success:
                if result:
                    if level[0]['level'] >=need :
                        log("操作人权限符合要求")
                        return "成功"
                    else :
                        log("操作人权限不足")
                        return "操作人权限不足"
                else:
                    error("读取操作人权限失败")
                    return "读取操作人权限失败"
            else:
                error("当前check失败" )
                return "数据库读取失败"
        else:
            notice("id不存在")
            return "操作人id不存在"
    else:
        error("当前check失败" )
        return "数据库读取失败"

def student_login_check(mysqls,username,password):
    success, result, _ = mysqls.query("SELECT sname FROM student WHERE susername = '{}';".format(username))
    if success:
        if result:
            log("id存在")
            success_p, _, pmd5 = mysqls.query("SELECT spassword FROM student WHERE susername = '{}';".format(username))
            if success_p:
                log("获取密码成功,用户输入密码是{},获得的密码是{}".format(str(password),str(pmd5[0]['spassword'])))
                if str(pmd5[0]['spassword']) == str(password):
                    log("密码正确")
                    return "密码正确"
                else:
                    log("密码错误")
                    return "密码错误"
            else:
                error("获取密码失败")
                return "获取密码失败"
        else:
            log("用户不存在")
            return "用户不存在"
    else:
        error("当前check失败")
        return "数据库读取失败"


def teacher_login_check(mysqls,username,password):
    success, result, _ = mysqls.query("SELECT tname FROM teacher WHERE tusername = '{}';".format(username))
    if success:
        if result:
            log("id存在")
            success_p, _, pmd5 = mysqls.query("SELECT * FROM teacher WHERE tusername = '{}';".format(username))
            if success_p:
                log("获取密码成功,用户输入密码是{},获得的密码是{}".format(str(password),str(pmd5[0]['tpassword'])))
                if str(pmd5[0]['tpassword']) == str(password):
                    log("密码正确")
                    return "密码正确",pmd5[0]
                else:
                    log("密码错误")
                    return "密码错误",None
            else:
                error("获取密码失败")
                return "获取密码失败",None
        else:
            log("用户不存在")
            return "用户不存在",None
    else:
        error("当前check失败")
        return "数据库读取失败",None

def student_register(mysql,opusername,username,name, password):

    backker = level_check(mysql,opusername,2)
    if backker != '成功' :
        return backker

    success, result, _ = mysql.query("SELECT sid FROM student WHERE susername = '{}';".format(username))
    if success:
        if result:
            log("id存在")
            return "该成员id已存在"
        else:
            log("id不存在")
            success_s, result_s, _ = mysql.query("INSERT INTO student VALUES(0,'{}','{}','{}')".format(name,password,username))
            if success_s:
                if result_s:
                    log("成员插入成功")
                    return "成员插入成功"
                else:
                    notice("成员插入失败")
                    return "成员插入失败"
    else:
        error("当前check失败")
        return "数据库读取失败"

def teacher_register(mysql,opusername,username,name, password,level):

    backker = level_check(mysql,opusername,2)
    if backker != '成功' :
        return backker

    success, result, _ = mysql.query("SELECT tid FROM teacher WHERE tusername = '{}';".format(username))
    if success:
        if result:
            log("id存在")
            return "该成员id已存在"
        else:
            log("id不存在")
            success_s, result_s, _ = mysql.query("INSERT INTO teacher VALUES(0,'{}','{}','{}',{})".format(name,password,username,str(level)))
            if success_s:
                if result_s:
                    log("成员插入成功")
                    return "成员插入成功"
                else:
                    notice("成员插入失败")
                    return "成员插入失败"
    else:
        error("当前check失败")
        return "数据库读取失败"

def search_teacher(mysql,t_username):
    success, result, t = mysql.query("SELECT * FROM teacher WHERE tusername = '{}';".format(t_username))
    if success:
        if result:
            return 1,t[0]
    error("获取失败")
    return -1,None

def search_student(mysql,s_username):
    success, result, s = mysql.query("SELECT * FROM student WHERE susername = '{}';".format(s_username))
    if success:
        if result:
            return 1,s[0]
    error("获取失败")
    return -1,None

def search_class(mysql,cid):
    success, result, c = mysql.query("SELECT * FROM class WHERE cid = '{}';".format(cid))
    if success:
        if result:
            return 1,c[0]
    error("获取失败")

    return -1,None

def search_job(mysql,jid):
    success, result, j = mysql.query("SELECT * FROM jobs WHERE jid = '{}';".format(jid))
    if success:
        if result:
            return 1,j[0]
    error("获取失败")
    return -1,None

def get_teacher_classes(mysql,t_username):
    tid = search_teacher(mysql,t_username)[1]['tid']
    success, result, classlist = mysql.query("SELECT class.cid,class.tid,class.cname,class.cnote,class.cnum,teacher.tusername,teacher.tname FROM class join teacher on class.tid = teacher.tid WHERE class.tid = '{}';".format(tid))

    if success:
            log("获取教师课程列表成功")
            return 1,classlist
    error("获取失败")
    return -1,None

def get_student_classes(mysql,s_username):
    sid = search_student(mysql,s_username)[1]['sid']
    success, result, classlist = mysql.query("SELECT class.cid,class.tid,class.cname,class.cnote,class.cnum,class.cnumber,student_class.sid,student_class.note,teacher.tname,teacher.tusername FROM (class join student_class on class.cid = student_class.cid ) join teacher on class.tid = teacher.tid where student_class.sid = '{}';".format(sid))
    if success:
        if result:
            log("获取学生课程列表成功")
            return 1,classlist
    error("获取失败")
    return -1,None

def job_repeat_check(mysql,username,cid,number):
    s = search_student(mysql,username)
    if not s[1]:
        return -1,None
    success, result, _ = mysql.query("SELECT jid FROM jobs WHERE sid = '{}' and cid = {} and number = {};".format(s[1]['sid'],cid,number))
    if success:
        if result:
            log("这一编号的作业已经存在！")
            return -1,None
    return 1,None

def job_information_up(mysql,username,title,note,cid,datetimes,url,number):
    if job_repeat_check(mysql,username,cid,number)[0] is -1:
        return -1, None
    t = search_class(mysql,cid)
    s = search_student(mysql,username)
    if not t[1] or not s[1]:
        return -1,None
    tid = t[1]['tid']
    sid = s[1]['sid']
    ts = title
    ns = note
    cids = cid
    create_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    u = url
    staus = 0
    success, result, _ = \
        mysql.query("INSERT INTO jobs VALUES(0,{},{},'{}','{}',{},'{}',{},NULL,'{}',NULL,{});"
                    .format(tid, sid, ts,ns,cid,create_time,staus,u,number))
    if success:
        if result:
            success, result, jobs = \
                mysql.query("SELECT * FROM jobs WHERE tid ={} and cid={} and sid={} and number={};"
                            .format(tid,cid,sid,number))
            log("插入作业成功")
            return 1,jobs[0]
    error("插入作业失败")
    return -1,None

def list_split(items, n):
    return [items[i:i+n] for i in range(0, len(items), n)]

def get_job(mysql,username,cid,order,pages,page_size,number,teacherid):
    if username is "all": sid = -1
    else: sid = search_student(mysql,username)[1]['sid']
    if not sid : return -1,None
    querys = "SELECT * FROM jobs "
    whereargs = []
    if sid is not -1: whereargs.append(" sid={} ".format(sid))
    if cid is not -1: whereargs.append(" cid={} ".format(cid))
    if number is not -1: whereargs.append(" number={} ".format(number))
    if teacherid is not -1: whereargs.append(" teacherid={} ".format(teacherid))
    wherearg = 'WHERE' + ('AND'.join(whereargs))
    querys += wherearg
    '''
    if sid is -1 and cid is -1:querys = "SELECT * FROM jobs"
    elif sid is -1: querys = "SELECT * FROM jobs WHERE cid = '{}'"\
            .format(cid)
    elif cid is -1: querys = "SELECT * FROM jobs WHERE sid = '{}'"\
            .format(sid)
    elif 
    else: querys = "SELECT * FROM jobs WHERE sid = '{}' and cid='{}'"\
            .format(sid,cid)
    '''

    '''
        order list
        0 : 按照默认顺序排列
        1 : 按照学生id升序排列
        2 ： 按照学生id降序排列
        3 ： 按照课程id升序排列
        4 ： 按照课程id降序排列
        5 ： 按照时间升序排列
        6 ： 按照时间降序排列
        7 :  按照number升序排列
        8 ： 按照number降序排列
    '''

    if order is 1: querys += "order by sid asc;"
    elif order is 2: querys += "order by sid desc;"
    elif order is 3: querys += "order by cid asc;"
    elif order is 4: querys += "order by cid desc;"
    elif order is 5: querys += "order by times asc;"
    elif order is 6: querys += "order by times desc;"
    elif order is 7: querys += "order by number asc;"
    elif order is 8: querys += "order by number desc;"
    elif int(order) <= 0: querys += ";"

    success, result, jobs = mysql.query(querys)
    if success:
                log("获取作业列表列表成功")
                nums =len(jobs)
                page_re = 0
                if pages is not -1:
                    page_get=list_split(jobs,page_size)

                    if int(pages)>len(page_get):
                        page_re = len(page_get)-1
                    else: page_re = int(pages)-1
                    if len(page_get) >= 1:
                        return 1, {"total_size": nums, "page_size": page_size, "data": page_get[page_re]}
                    else:
                        return 1, {"total_size": nums, "page_size": page_size, "data": None}
                else:
                    page_get = jobs
                    return 1, {"total_size":nums,"page_size":page_size,"data":page_get}
    error("获取失败")
    return -1, None

def delete_job(mysql,jid):
    success, result, _ = mysql.query("delete from jobs where jid = {};".format(jid))
    if success:
        if result:
            log("删除成功")
            return 1,None
    error("删除失败")
    return -1,None

def job_update(mysql,title,note,jid,url,urlbool,comment):
    up_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    if title is not -1:
        success, result, _ = \
            mysql.query("UPDATE jobs set title='{}' where jid={};"
                        .format(title,jid))
        if not success:return -1, None

    if note is not -1:
        success, result, _ = \
            mysql.query("UPDATE jobs set note='{}' where jid={};"
                        .format(note, jid))
        if not success:return -1, None

    if comment is not -1:
        success, result, _ = \
            mysql.query("UPDATE jobs set comment='{}' where jid={};"
                        .format(comment, jid))
        if not success:return -1, None

    if urlbool is True:
        success, result, _ = \
            mysql.query("UPDATE jobs set url='{}' where jid={};"
                        .format(url, jid))
        if not success:return -1, None

    success, result, _ = \
        mysql.query("UPDATE jobs set times='{}' where jid={};"
                    .format(up_time, jid))
    if not success: return -1, None

    return 1,None

def get_job_score(mysql,username,jid):
    sid = search_student(mysql,username)[1]['sid']
    success, result, score = mysql.query("select score,staus,comment from jobs where sid = {} and sid = {};").format(sid,jid)
    if success:
        if result:
            return 1,{'jid':jid,'sid':sid,'score':score[0]['score'],'staus':score[0]['staus'],"comment":score[0]['comment']}
    error("获取失败")
    return -1, None

def get_class_score(mysql,username,cid):
    sid = search_student(mysql,username)[1]['sid']
    success, result, scores = mysql.query("select score,staus from jobs where sid = {} and cid = {} and staus = 1;".format(sid,cid))
    if success:
            print(scores)
            sum = 0
            numbers = 0
            averagescore = 0,
            for i in scores:
                sum += i['score']
                numbers += 1
            if numbers is not 0 : averagescore = sum / numbers
            print({'cid': cid, 'sid': sid, 'jobnums': numbers, 'total': sum, "average": averagescore, "alljob": scores})
            return 1,{'cid':cid,'sid':sid,'jobnums': numbers ,'total':sum,"average" : averagescore,"alljob":scores}
    error("获取失败")
    return -1, None



def get_classes_student(mysql,cid):
    success, result, students = mysql.query("select * from student join (select sid from student_class where cid = {}) as newf on student.sid = newf.sid"
                                             .format(cid))
    for index,i in enumerate(students):
        backinf = get_class_score(mysql,i['susername'],cid)
        if backinf[0] is -1:
            error("查找统计出错")
            return -1,None
        else: students[index].update({"score_information":backinf[1]})
    if success:
            log("获取学生列表成功")
            return 1,students
    error("获取失败")
    return -1,None


def give_score(mysql,jid,score,comment):
    success, result, _ = mysql.query("UPDATE jobs set score={} , staus = 1 , comment = '{}' where jid={}; ".format(str(score),str(comment),str(jid)))
    if success:
            log("给分成功")
            return 1,None
    error("给分失败")
    return -1,None

def class_new_round(mysql,cid,note):
    round = get_all_round(mysql, cid)[1]
    success, result, _ = \
        mysql.query("UPDATE class SET cnumber = {} WHERE cid ={};"
                    .format(str(round+1),str(cid)))
    if success:
        if result:
            success, result, _ = mysql.query(
                    "INSERT INTO task VALUES(0,'{}',{},{});"
                    .format(str(note), str(cid), str(round+1)))
            if success:
                if result:
                    log("插入任务成功")
                    return 1,None
    error("插入任务失败")
    return -1,None

def task_search(mysql,cid,number):
    querys="SELECT * FROM task WHERE cid ={} order by num asc".format(str(cid))
    if number is not -1:
        querys+="and number = {};".format(str(number))
    else: querys+=";"
    success, result, tasks = \
        mysql.query(querys)
    if success:
        if result:
            log("搜索任务成功")
            return 1,tasks
    error("搜索任务失败")
    return -1,None

def get_all_round(mysql,cid):
    success, result, back = mysql.query("SELECT cnumber from class where cid={};".format(str(cid)))
    if success:
        if result:
            log("获取成功")
            return 1,int(back[0]['cnumber'])
    error("获取失败")
    return -1,None

def get_student_homework(mysql,username,cid):
    round = get_all_round(mysql,cid)[1]

    sid = search_student(mysql, username)[1]['sid']
    success, result, back = mysql.query("SELECT number from jobs where cid={} and sid={};".format(str(cid),str(sid)))
    if success:
            log("获取成功")
            works = []
            if len(back) is not 0:
                for i in back:
                    works.append(int(i['number']))
            unfinished = []
            if round is not 0:
                for t in range(1,int(round)+1):
                    if not (t in works):
                        unfinished.append(t)
            return 1, {"allwork":round,"finishwork":works,"unfinishedwork":unfinished}
    error("获取失败")
    return -1,None

def teacher_search(mysql,passw,type):
    if type is 1:
        querys="SELECT * FROM teacher WHERE tusername ={}".format(str(passw))
    else:
        querys = "SELECT * FROM teacher WHERE tid ={}".format(str(passw))
    success, result, teacher = \
        mysql.query(querys)
    if success:
        if result:
            log("搜索教师成功")
            return 1,teacher
    error("搜索教师失败")
    return -1,None

def new_class(mysql,cname,cnote,tusername):
    tid = search_teacher(mysql, tusername)[1]['tid']
    success, result, _ = \
        mysql.query("INSERT INTO class VALUES(0,{},'{}',{},0,0);".format(str(tid),str(cname),str(cnote)))
    if success:
        if result:
            log("插入班级成功")
            return 1,None
    error("插入班级失败")
    return -1,None

def insert_student_intoclass(mysql,username,cid):
    sid = search_student(mysql, username)[1]['sid']
    success, result, _ = \
        mysql.query("INSERT INTO student_class VALUES({},{},'');".format(str(sid),str(cid)))
    if success:
        if result:
            success, result, _ = mysql.query("UPDATE class SET cnum=cnum+1 WHERE cid ={};".format(str(cid)))
            if success:
                if result:
                    log("插入学生成功")
                    return 1,None
    error("插入学生失败")
    return -1,None